
/**
	AUTHOR		: VEERU
	DATE		: SEP 21st 2005	
	DESCRIPTION	: THIS PROCEDURE IS USED TO LOAD ALL THE SUBSTITUTABLE LOTS FOR THE CLASS STORE PART NUMBER
	USAGE           : EXEC SP_LOAD_SUBSTITUTABLES_LOTS 1080, 473,  '11292'
*/			

ALTER PROC SP_LOAD_SUBSTITUTABLES_LOTS( @PROCESSSTEP_ID INT, @RELEASE_ID INT, @PROCESSSTEP_IDS VARCHAR(1000) )
AS
BEGIN
	DECLARE @LOCATION_ID INT	
	create table #RESULTS (LOT_ID INT, LOT_NUMBER VARCHAR(50), QTY_IN INT, BALANCE_QTY INT, QUANTITY_PULL INT, QTY_SHIPPED INT )
	DECLARE @SQL_QUERY NVARCHAR(2000)
	DECLARE @PARAMS NVARCHAR(1000)
	DECLARE @PROG_REL_STATUS VARCHAR(50)
	SELECT @PROG_REL_STATUS = STATUS FROM PROGRAM_RELEASE WHERE PROG_RELEASE_ID = @RELEASE_ID	
	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = 'CLASS STORE' AND VENDOR_ID = ( SELECT VENDOR_ID FROM PROGRAM_RELEASE WHERE PROG_RELEASE_ID = @RELEASE_ID )
	SET @SQL_QUERY = N'INSERT INTO #RESULTS SELECT LT.LOT_ID, LT.LOT_NUMBER, IL.IL_QTY_IN, 
		IL.IL_QTY_IN - ISNULL( ( SELECT SUM( QTY_PULLED ) FROM PROG_RELEASE_LOT_TABLE PRLT, PROGRAM_RELEASE PR WHERE PRLT.LOT_ID = LT.LOT_ID AND PR.PROG_RELEASE_ID = PRLT.PROG_RELEASE_ID AND PR.STATUS <> ''CANCELLED'' ), 0 ) +  ISNULL( ( SELECT QTY_PULLED FROM PROG_RELEASE_LOT_TABLE PRLT WHERE PRLT.LOT_ID = LT.LOT_ID AND PROG_RELEASE_ID = @RELEASE_ID ), 0 ) , 
		ISNULL( ( SELECT QTY_PULLED FROM PROG_RELEASE_LOT_TABLE PRLT WHERE PRLT.LOT_ID = LT.LOT_ID AND PROG_RELEASE_ID = @RELEASE_ID ), 0 ), 
		ISNULL( ( SELECT QTY_SHIPPED FROM PROG_RELEASE_LOT_TABLE PRLT WHERE PRLT.LOT_ID = LT.LOT_ID AND PROG_RELEASE_ID = @RELEASE_ID ), 0 ) 
		FROM LOT_TABLE LT, INVENTORY_LOT IL WHERE LT.LOT_ID = IL.LOT_ID
		AND PROCESSSTEP_ID IN (' + cast(@PROCESSSTEP_IDS as nvarchar) + ') AND LOT_CURRENT_STATUS = ''RUNNING'' AND IS_HOLD != 0 AND LT.LOCATION_ID = @LOCATION_ID
 		UNION ( SELECT LT.LOT_ID, LT.LOT_NUMBER, IL.IL_QTY_IN, 
 		IL.IL_QTY_IN - ISNULL( ( SELECT SUM( QTY_PULLED ) FROM PROG_RELEASE_LOT_TABLE PRLT, PROGRAM_RELEASE PR WHERE PRLT.LOT_ID = LT.LOT_ID AND PR.PROG_RELEASE_ID = PRLT.PROG_RELEASE_ID AND PR.STATUS <> ''CANCELLED'' ), 0 ) +  ISNULL( ( SELECT QTY_PULLED FROM PROG_RELEASE_LOT_TABLE PRLT WHERE PRLT.LOT_ID = LT.LOT_ID AND PROG_RELEASE_ID = @RELEASE_ID ), 0 ) , 
 		ISNULL( ( SELECT QTY_PULLED FROM PROG_RELEASE_LOT_TABLE PRLT WHERE PRLT.LOT_ID = LT.LOT_ID AND PROG_RELEASE_ID = @RELEASE_ID ), 0 ), 
		ISNULL( ( SELECT QTY_SHIPPED FROM PROG_RELEASE_LOT_TABLE PRLT WHERE PRLT.LOT_ID = LT.LOT_ID AND PROG_RELEASE_ID = @RELEASE_ID ), 0 ) 
 		FROM LOT_TABLE LT, INVENTORY_LOT IL, PROG_RELEASE_LOT_TABLE PRLT WHERE PRLT.LOT_ID = LT.LOT_ID AND LT.LOT_ID = IL.LOT_ID 
 		AND PRLT.PROG_RELEASE_ID = @RELEASE_ID  AND PROCESSSTEP_ID NOT IN ('+ cast(@PROCESSSTEP_ID as nvarchar) + ') AND LT.LOCATION_ID = @LOCATION_ID'
	IF (@PROG_REL_STATUS = 'APPROVED')
	BEGIN
		SET @SQL_QUERY = @SQL_QUERY + ' AND LOT_CURRENT_STATUS = ''RUNNING'' AND IS_HOLD != 0 )'
	END
	ELSE --CANCELLED, SHIPP
	BEGIN
		SET @SQL_QUERY = @SQL_QUERY + ')'
	END
	SET @PARAMS = N'@RELEASE_ID INT, @LOCATION_ID INT'
	print @sql_query
	EXECUTE SP_EXECUTESQL @SQL_QUERY, @PARAMS, @RELEASE_ID, @LOCATION_ID
	SELECT * FROM #RESULTS WHERE BALANCE_QTY > 0
END

